﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Xml;
using SaaS;
using System.Data;
public partial class AddData : System.Web.UI.Page
{
    string dataComment;
    protected void Page_Load(object sender, EventArgs e)
    {
        Label_attributes.Text = "";
        if (Session.Count != 0 & DropDownList_Object.Items.Count == 0)
        {
            string strConnection = "workstation id=MiniSaaS.mssql.somee.com;packet size=4096;user id=SaaS_cse564;pwd=123abccse;data source=MiniSaaS.mssql.somee.com;persist security info=False;initial catalog=MiniSaaS";
            System.Data.SqlClient.SqlConnection Connection = new System.Data.SqlClient.SqlConnection(strConnection);
            string loginID = Session["LoginID"].ToString();
            String strSQL = "SELECT DISTINCT Table_ID FROM META WHERE Login_ID = " + loginID + ";";
            System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(strSQL, Connection);
            System.Data.SqlClient.SqlDataReader Dr;
            Connection.Open();
            Dr = command.ExecuteReader();
            System.Collections.Generic.List<int> tableIDs = new System.Collections.Generic.List<int>();
            while (Dr.Read())
                tableIDs.Add((int)Dr["Table_ID"]);
            Dr.Close();
            for (int i = 0; i < tableIDs.Count; i++)
            {
                strSQL = "SELECT Table_Name FROM META WHERE Table_ID = " + tableIDs[i].ToString() + " AND Table_Version = (SELECT MAX(Table_Version) FROM META WHERE Table_ID = " + tableIDs[i].ToString()+")";
                command = new System.Data.SqlClient.SqlCommand(strSQL, Connection);
                Dr = command.ExecuteReader();
                Dr.Read();
                DropDownList_Object.Items.Add(Dr["Table_Name"].ToString());
                Session["TableID" + i.ToString()] = tableIDs[i].ToString();
                Dr.Close();
            }
            Connection.Close();
            Session["KeysAdded"] = 0;
            //DropDownList_Object_SelectedIndexChanged(null, null);
        }
    }
    protected void clearTempData()
    {
        DropDownList_Object_SelectedIndexChanged(null, null);
        Session["KeysAdded"] = 0;
    }
    protected void DropDownList_Object_SelectedIndexChanged(object sender, EventArgs e)
    {
        string selectVal = DropDownList_Object.SelectedValue;
        string TableName = selectVal;
        string tableID = Session["TableID" + DropDownList_Object.SelectedIndex.ToString()].ToString();
        int tableVer;

        string strConnection = "workstation id=MiniSaaS.mssql.somee.com;packet size=4096;user id=SaaS_cse564;pwd=123abccse;data source=MiniSaaS.mssql.somee.com;persist security info=False;initial catalog=MiniSaaS";
        System.Data.SqlClient.SqlConnection Connection = new System.Data.SqlClient.SqlConnection(strConnection);
        string loginID = Session["LoginID"].ToString();
        String strSQL = "SELECT MAX(Table_Version) FROM META WHERE Table_ID = " + tableID;
        System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(strSQL, Connection);
        System.Data.SqlClient.SqlDataReader Dr;
        Connection.Open();
        Dr = command.ExecuteReader();
        if (!Dr.HasRows)
        {
            //error - could not find reference to the Table_ID in database
            return;
        }
        Dr.Read();
        tableVer = (int)Dr[0];
        Dr.Close();
        strSQL = "Select Table_ID, Table_Columns From META WHERE Login_ID = " + loginID + "and Table_ID = "+tableID +" and Table_Version = " + tableVer.ToString() + ";";
        command = new System.Data.SqlClient.SqlCommand(strSQL, Connection);
        Dr = command.ExecuteReader();
        Dr.Read();
        string TableColumns = Dr["Table_Columns"].ToString();
        Session["xml"] = TableColumns;
        Session["TableID"] = Dr["Table_ID"].ToString();
        Dr.Close();
        XmlDocument xd = new XmlDocument();
        xd.LoadXml(TableColumns);
        XmlNodeList xnl = xd.SelectNodes("//col");

        XmlDocument xmldata = new XmlDocument();
        xmldata.LoadXml("<data></data>");
        DropDownList_Attributes.Items.Clear();
        bool first = true;
        foreach (XmlNode xn in xnl)
        {
            if (first)
            {
                //save the column ID of the first entered item
                if (xn.ChildNodes.Count >= 5)
                    //use the column ID
                    Session["ColumnID"] = xn.ChildNodes[4].InnerText.ToString();
                else
                    Session["ColumnID"] = "";
                first = false;
            }
            string nameT = xn.ChildNodes[0].InnerText + "(" +
                xn.ChildNodes[1].InnerText + ")";
            DropDownList_Attributes.Items.Add(nameT);

            XmlElement col = xmldata.CreateElement("col");
            XmlElement xmlD = xmldata.CreateElement("coldata");
            XmlElement xmlCID = xmldata.CreateElement("columnID");
            xmlD.InnerText = xn.ChildNodes[3].InnerText;
            col.AppendChild(xmlD);
            col.AppendChild(xmlCID);

            xmldata.DocumentElement.AppendChild(col);
        }
        xmldata.Save(Server.MapPath(@"App_Data\tempdata.xml"));
        DropDownList_Attributes_SelectedIndexChanged(null, null);
    }
    protected void BT_ADD_Data_Click(object sender, EventArgs e)
    {
        Label_attributes.Text = "";
        lblErr.Text = "";
        int selectVal = DropDownList_Attributes.SelectedIndex;
        //////////////////////////////////////////////////////////////////////////
        // check the type
        string selectValue = DropDownList_Attributes.SelectedValue;

        AddDataService ads = new AddDataService();
        if (ads.addData(selectVal, selectValue, Session["ColumnID"].ToString(), Session["isPriKey"].ToString(), TB_attrValue.Text.Trim()) == false)
            Label_attributes.Text = "Please input the right type";

        TB_attrValue.Text = "";

        DataSet dataSet = new DataSet();
        dataSet.ReadXml(Server.MapPath("~/App_Data/tempdata.xml"));
        this.GridView1.DataMember = "col";
        this.GridView1.DataSource = dataSet;
        this.GridView1.DataBind();
    }
    protected void DropDownList_Attributes_SelectedIndexChanged(object sender, EventArgs e)
    {
        int selectVal = DropDownList_Attributes.SelectedIndex;
        XmlDocument xd = new XmlDocument();
        xd.LoadXml(Session["xml"].ToString());
        XmlNodeList xnl = xd.SelectNodes("//col");
        string devalue = xnl[selectVal].ChildNodes[3].InnerText;
        if (xnl[selectVal].ChildNodes.Count >= 5)
        {
            //use the column ID
            XmlNode colNode = xnl[selectVal].ChildNodes[4];
            Session["ColumnID"] = colNode.InnerText.ToString();
            XmlNode priKeyNode = colNode.NextSibling;
            if (priKeyNode != null)
                Session["isPriKey"] = priKeyNode.InnerText.ToString();
            else
                Session["isPriKey"] = "False";
        }
        else
        {
            Session["ColumnID"] = "";
            Session["isPriKey"] = "False";
        }

        this.TB_attrValue.Text = devalue;
    }

    protected void FINISH_Data_Click(object sender, EventArgs e)
    {
        Label_attributes.Text = "";
        lblErr.Text = "";
        dataComment = this.TB_dataComments.Text;
        AddDataService ads = new AddDataService();
        string errVal = ads.finish(dataComment, Session["TableID"].ToString());
        if (errVal.Length > 0)
            lblErr.Text = errVal;
        clearTempData();
    }
}